﻿'*******************************************************************
' Program: Data Center Visio Rack Tool
' Author: Albert E Edlund
' Date:
'
' Purpose:
'
'
'*******************************************************************

Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging


Imports System
Imports System.ComponentModel
Imports System.Collections.Generic
Imports System.Collections.ObjectModel


Imports Microsoft.Office.Interop.Visio
Imports System.Runtime.InteropServices

Imports System.Data
Imports System.Array
Imports System.Data.Common
Imports Microsoft.SqlServer
Imports System.Xml
Imports System.IO

Imports System.Drawing

Imports visRackComponentAttributes.clsVisioSupport
' for those with enumerations
Imports visRackComponentAttributes.dc_Product
Imports visRackComponentAttributes.clsDC_RackData
Imports visRackComponentAttributes.clsDC_RackData.rackURRI



Public Class clsComponents

#Region " Properties and definitions "


    Public m_db As Database
    Public m_ds As DataSet
    Public m_dt As DataTable

    Public m_tempds As DataSet
    Public m_tempdt As DataTable

    Public m_dc As dc_Component
    Public m_oc As Collection(Of dc_Component)

    Private strUserId As String


    Public _clsVisio As visRackComponentAttributes.clsVisioSupport

    ' who am I
    Private myEnum As DC_RackTables = DC_RackTables.tblComponents

    Private m_deleteCommand As DbCommand
    Private m_insertCommand As DbCommand
    Private m_updateCommand As DbCommand


    Public m_dt_xml As Xml.XmlDocument

    Public Const readAll As String = "selectTblComponents"
    Public Const rptAll As String = "rptComponentsAll"

    Private m_parentform As Object 'frmProductVal
    Public Property _ParentForm() As Object
        Get
            Return Me.m_parentform
        End Get
        Set(ByVal value As Object)
            Me.m_parentform = value
        End Set
    End Property

    '********* visio snippet vb.net vis0041vb ******************
    Private _drawingApplication As Microsoft.Office.Interop.Visio.Application
    Public Property drawingApplication() As Microsoft.Office.Interop.Visio.Application
        Get
            Return Me._drawingApplication
        End Get
        Set(ByVal value As Microsoft.Office.Interop.Visio.Application)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("ApplicationValueIsNull", _
                    "drawingApplication is Null.")
            End If
            Me._drawingApplication = value
        End Set
    End Property

    '********* visio snippet vb.net vis0043vb ******************
    Private _drawingDocument As Microsoft.Office.Interop.Visio.Document
    Public Property drawingDocument() As Microsoft.Office.Interop.Visio.Document
        Get
            Return Me._drawingDocument
        End Get
        Set(ByVal value As Microsoft.Office.Interop.Visio.Document)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("DocumentValueIsNull", _
                    "drawingDocument is Null.")
            End If
            Me._drawingDocument = value
        End Set
    End Property

    '********* visio snippet vb.net vis0045vb ******************
    Private _drawingWindow As Microsoft.Office.Interop.Visio.Window
    Public Property drawingWindow() As Microsoft.Office.Interop.Visio.Window
        Get
            Return Me._drawingWindow
        End Get
        Set(ByVal value As Microsoft.Office.Interop.Visio.Window)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("WindowValueIsNull", _
                    "drawingWindow is Null.")
            End If
            Me._drawingWindow = value
        End Set
    End Property

    '********* visio snippet vb.net vis0044vb ******************
    Private _drawingPage As Microsoft.Office.Interop.Visio.Page
    Public Property drawingPage() As Microsoft.Office.Interop.Visio.Page
        Get
            Return Me._drawingPage
        End Get
        Set(ByVal value As Microsoft.Office.Interop.Visio.Page)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("PageValueIsNull", _
                    "drawingPage is Null.")
            End If
            Me._drawingPage = value
        End Set
    End Property

    Public m_customstencilpath As String = My.Settings.path_custom_stencils
    Public Property _CustomStencilPath() As String
        Get
            Return Me.m_customstencilpath
        End Get
        Set(ByVal value As String)
            Me.m_customstencilpath = value
        End Set
    End Property

#End Region

#Region " Move/Transform Data "

    '
    ' create columns
    '
    Public Sub CreateTableColumns(ByRef tempTable As DataTable)

        Dim newDC As dc_Component = New dc_Component(strUserId)

        Try
            Dim colCompKey As New System.Data.DataColumn
            colCompKey.ColumnName = "compKey"
            colCompKey.DataType = GetType(Integer)
            colCompKey.DefaultValue = newDC.constCompKey
            tempTable.Columns.Add(colCompKey)

            Dim colCompLocKey As New System.Data.DataColumn
            colCompLocKey.ColumnName = "compLocKey"
            colCompLocKey.DataType = GetType(Integer)
            colCompLocKey.DefaultValue = newDC.constCompLocKey
            tempTable.Columns.Add(colCompLocKey)

            Dim colCompName As New System.Data.DataColumn
            colCompName.ColumnName = "compName"
            colCompName.DataType = GetType(String)
            colCompName.DefaultValue = newDC.constCompName
            tempTable.Columns.Add(colCompName)

            Dim colCompLabel As New System.Data.DataColumn
            colCompLabel.ColumnName = "compLabel"
            colCompLabel.DataType = GetType(String)
            colCompLabel.DefaultValue = newDC.constCompLabel
            tempTable.Columns.Add(colCompLabel)

            Dim colCompVendorKey As New System.Data.DataColumn
            colCompVendorKey.ColumnName = "compVendorKey"
            colCompVendorKey.DataType = GetType(Integer)
            colCompVendorKey.DefaultValue = newDC.constCompVendorKey
            tempTable.Columns.Add(colCompVendorKey)

            Dim colcompParentSlot As New System.Data.DataColumn
            colcompParentSlot.ColumnName = "compParentSlot"
            colcompParentSlot.DataType = GetType(String)
            colcompParentSlot.DefaultValue = newDC.constcompParentSlot
            tempTable.Columns.Add(colcompParentSlot)

            Dim colCompComments As New System.Data.DataColumn
            colCompComments.ColumnName = "compComments"
            colCompComments.DataType = GetType(String)
            colCompComments.DefaultValue = newDC.constCompComments
            tempTable.Columns.Add(colCompComments)


            Dim colCompParentKey As New System.Data.DataColumn
            colCompParentKey.ColumnName = "compParentKey"
            colCompParentKey.DataType = GetType(Integer)
            colCompParentKey.DefaultValue = newDC.constCompParentKey
            tempTable.Columns.Add(colCompParentKey)

            Dim colCompParentSubType As New System.Data.DataColumn
            colCompParentSubType.ColumnName = "compParentSubType"
            colCompParentSubType.DataType = GetType(Integer)
            colCompParentSubType.DefaultValue = newDC.constCompParentSubType
            tempTable.Columns.Add(colCompParentSubType)

            Dim colCompMoveStatus As New System.Data.DataColumn
            colCompMoveStatus.ColumnName = "compMoveStatus"
            colCompMoveStatus.DataType = GetType(Integer)
            colCompMoveStatus.DefaultValue = newDC.constCompMoveStatus
            tempTable.Columns.Add(colCompMoveStatus)

            Dim colCompType As New System.Data.DataColumn
            colCompType.ColumnName = "compType"
            colCompType.DataType = GetType(Integer)
            colCompType.DefaultValue = newDC.constCompType
            tempTable.Columns.Add(colCompType)

            Dim colcompSubType As New System.Data.DataColumn
            colcompSubType.ColumnName = "compSubtype"
            colcompSubType.DataType = GetType(Integer)
            colcompSubType.DefaultValue = newDC.constCompSubtype
            tempTable.Columns.Add(colcompSubType)

            Dim colCompLastUpdate As New System.Data.DataColumn
            colCompLastUpdate.ColumnName = "compLastUpdate"
            colCompLastUpdate.DataType = GetType(Date)
            colCompLastUpdate.DefaultValue = newDC.constCompLastUpdate
            tempTable.Columns.Add(colCompLastUpdate)

            Dim colCompLastEdit As New System.Data.DataColumn
            colCompLastEdit.ColumnName = "compLastEdit"
            colCompLastEdit.DataType = GetType(String)
            colCompLastEdit.DefaultValue = newDC.constCompLastEdit
            tempTable.Columns.Add(colCompLastEdit)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        If newDC IsNot Nothing Then
            newDC = Nothing
        End If

    End Sub

    '
    ' use this initially for adding rows
    '
    Public Sub movePropertiesToDataRow _
              (ByRef tempRow As DataRow, _
                ByVal tempDC As dc_Component)

        Try
            With tempDC
                tempRow.SetField("compKey", .compKey)
                tempRow.SetField("compLocKey", .compLocKey)
                tempRow.SetField("compName", .compName)
                tempRow.SetField("compLabel", .compLabel)
                tempRow.SetField("compVendorKey", .compVendorKey)
                tempRow.SetField("compParentSlot", .compParentSlot)
                tempRow.SetField("compComments", .compComments)
                tempRow.SetField("compParentKey", .compParentKey)
                tempRow.SetField("compParentSubtype", .compParentSubType)
                tempRow.SetField("compMoveStatus", .compMoveStatus)
                tempRow.SetField("compType", .compType)
                tempRow.SetField("compSubtype", .compSubtype)
                tempRow.SetField("compLastUpdate", .compLastUpdate)
                tempRow.SetField("compLastEdit", .compLastEdit)
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' pass in a datarow and move it to the properties
    '
    Public Sub moveDataRowToProperties _
            (ByRef tempRow As DataRow)

        Try

            With m_dc
                .compKey = tempRow.Item("compKey")
                .compLocKey = tempRow.Item("compLocKey")
                .compName = tempRow.Item("compName")

                If Not (IsDBNull(tempRow.Item("compLabel"))) Then
                    .compLabel = tempRow.Item("compLabel")
                Else
                    .compLabel = .constCompLabel
                End If

                .compVendorKey = tempRow.Item("compVendorKey")
                .compParentSlot = tempRow.Item("compParentSlot")
                .compComments = tempRow.Item("compComments")

                If Not (IsDBNull(tempRow.Item("compParentKey"))) Then
                    .compParentKey = tempRow.Item("compParentKey")
                Else
                    .compParentKey = .constCompParentKey
                End If
                .compParentSubType = tempRow.Item("compParentSubtype")
                .compMoveStatus = tempRow.Item("compMoveStatus")

                .compType = tempRow.Item("compType")
                .compSubtype = tempRow.Item("compSubtype")

                If Not (IsDBNull(tempRow.Item("compLastEdit"))) Then
                    .compLastEdit = tempRow.Item("compLastEdit")
                Else
                    .compLastEdit = .constCompLastEdit
                End If

                .compLastUpdate = tempRow.Item("compLastUpdate")
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Sub moveDataRowToProperties _
             (ByVal tempRow As DataRow, _
             ByRef tempDC As dc_Component)

        Try

            With tempDC
                .compKey = tempRow.Item("compKey")
                .compLocKey = tempRow.Item("compLocKey")
                .compName = tempRow.Item("compName")

                If Not (IsDBNull(tempRow.Item("compLabel"))) Then
                    .compLabel = tempRow.Item("compLabel")
                Else
                    .compLabel = .constCompLabel
                End If

                .compVendorKey = tempRow.Item("compVendorKey")
                .compParentSlot = tempRow.Item("compParentSlot")
                .compComments = tempRow.Item("compComments")

                If Not (IsDBNull(tempRow.Item("compParentKey"))) Then
                    .compParentKey = tempRow.Item("compParentKey")
                Else
                    .compParentKey = .constCompParentKey
                End If
                .compParentSubType = tempRow.Item("compParentSubType")
                .compMoveStatus = tempRow.Item("compMoveStatus")

                .compType = tempRow.Item("compType")
                .compSubtype = tempRow.Item("compSubtype")


                If Not (IsDBNull(tempRow.Item("compLastEdit"))) Then
                    .compLastEdit = tempRow.Item("compLastEdit")
                Else
                    .compLastEdit = .constCompLastEdit
                End If

                .compLastUpdate = tempRow.Item("compLastUpdate")
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    '
    '
    Public Sub moveDataTableToObservableCollection _
            (ByVal dtTemp As DataTable)

        Try

            If 0 < dtTemp.Rows.Count Then

                If m_oc IsNot Nothing Then
                    m_oc = Nothing
                    m_oc = New Collection(Of dc_Component)
                Else
                    m_oc = New Collection(Of dc_Component)
                End If

                Dim drTemp As DataRow
                Dim dcTemp As dc_Component
                For Each drTemp In dtTemp.Rows
                    dcTemp = New dc_Component
                    moveDataRowToProperties(drTemp, dcTemp)
                    m_oc.Add(dcTemp)
                Next

            End If

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    '
    '
    Public Function moveDataTableToXML() As XmlDocument

        Dim writer = New System.IO.StringWriter
        Dim m_dt_xml As Xml.XmlDocument = Nothing

        Try


            m_dt.WriteXml(writer, XmlWriteMode.WriteSchema, True)
            m_dt_xml = Nothing
            m_dt_xml = New Xml.XmlDocument
            m_dt_xml.LoadXml(writer.ToString)

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally

            writer = Nothing

        End Try

        Return m_dt_xml


    End Function


#End Region

#Region " Standard Database Commands "

    '
    ' clear the general dataset and table, create columns in the table, and move it to
    ' the dataset
    '
    Public Sub initDataSetAndTable()

        Try

            ' make sure the root stuff is there
            If m_ds Is Nothing Then
                m_ds = New DataSet
            Else
                m_ds = Nothing
                m_ds = New DataSet
            End If
            m_ds.SchemaSerializationMode = SchemaSerializationMode.IncludeSchema

            If m_dc Is Nothing Then
                m_dc = New dc_Component(strUserId)
            Else
                m_dc = Nothing
                m_dc = New dc_Component(strUserId)
            End If

            If m_dt Is Nothing Then
                m_dt = New DataTable
            Else
                m_dt = Nothing
                m_dt = New DataTable
            End If

            If m_oc Is Nothing Then
                m_oc = New Collection(Of dc_Component)
            Else
                m_oc = Nothing
                m_oc = New Collection(Of dc_Component)
            End If

            CreateTableColumns(m_dt)
            m_dt.TableName = DC_RackTables.tblComponents.ToString

            m_ds.Tables.Add(m_dt)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub


    '
    ' insert command
    '
    Private Sub buildInsertComponentCommand()



        Try


            m_insertCommand = m_db.GetStoredProcCommand("insertComponent")
            Dim cmdActive As DbCommand
            cmdActive = m_insertCommand
            m_db.AddInParameter(cmdActive, "compLocKey", DbType.Int32, "compLocKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compName", DbType.String, "compName", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compLabel", DbType.String, "compLabel", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compParentSlot", DbType.String, "compParentSlot", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compComments", DbType.String, "compComments", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compParentKey", DbType.Int32, "compParentKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compParentSubType", DbType.Int16, "compParentSubType", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compMoveStatus", DbType.String, "compMoveStatus", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compType", DbType.Int16, "compType", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compSubType", DbType.Int16, "compSubtype", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compVendorKey", DbType.Int32, "compVendorKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compLastUpdate", DbType.Date, Now)
            m_db.AddInParameter(cmdActive, "compLastEdit", DbType.String, "compLastEdit", DataRowVersion.Current)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' update command
    '
    Private Sub buildUpdateComponentCommand()


        Try
            m_updateCommand = m_db.GetStoredProcCommand("updateComponent")

            Dim cmdActive As DbCommand
            cmdActive = m_updateCommand

            m_db.AddInParameter(cmdActive, "compKey", DbType.Int32, "compKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compLocKey", DbType.Int32, "compLocKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compName", DbType.String, "compName", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compLabel", DbType.String, "compLabel", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compParentSlot", DbType.String, "compParentSlot", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compComments", DbType.String, "compComments", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compParentKey", DbType.Int32, "compParentKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compParentSubType", DbType.Int16, "compParentSubType", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compMoveStatus", DbType.String, "compMoveStatus", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compType", DbType.Int16, "compType", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compSubType", DbType.Int16, "compSubtype", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compVendorKey", DbType.Int32, "compVendorKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "compLastUpdate", DbType.Date, Now)
            m_db.AddInParameter(cmdActive, "compLastEdit", DbType.String, "compLastEdit", DataRowVersion.Current)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try
    End Sub


    '
    ' delete command
    '
    Private Sub buildDeleteComponentCommand()


        Try

            m_deleteCommand = m_db.GetStoredProcCommand("deleteComponent")
            Dim cmdActive As DbCommand
            cmdActive = m_deleteCommand
            m_db.AddInParameter(cmdActive, "compKey", DbType.Int32, "compKey", DataRowVersion.Current)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' create a record based on property values move it to the generic datatable
    ' then copy the table to the database
    '
    Public Function addComponentDataSourceRecord _
            (ByVal tempDC As dc_Component, _
             Optional ByVal blnSilent As Boolean = False) _
            As Integer

        Dim intRowsAffected As Integer = 0

        Try

            initDataSetAndTable()

            Dim m_dr As DataRow = m_ds.Tables(DC_RackTables.tblComponents.ToString).NewRow
            movePropertiesToDataRow(m_dr, tempDC)
            m_ds.Tables(DC_RackTables.tblComponents.ToString).Rows.Add(m_dr)
            intRowsAffected = dbUpdateDataSet()


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        If blnSilent = False Then
            MsgBox("DataRowsUpated = " & intRowsAffected)
        End If

        Return intRowsAffected

    End Function


    '
    ' take the working datatable and update the database 
    '
    Public Function dbUpdateDataSet() As Int16

        Dim rowsAffected As Int16 = 0

        Try

            rowsAffected = m_db.UpdateDataSet(m_ds, DC_RackTables.tblComponents.ToString, _
                                        m_insertCommand, _
                                        m_updateCommand, _
                                        m_deleteCommand, _
                                        UpdateBehavior.Standard)
            'msgbox rowsAffected

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If

        End Try

        Return rowsAffected

    End Function


#End Region

#Region " database queries "


    '
    ' discrete record delete
    '
    Public Function dbDeleteComponent(ByVal intCompKey As Int32) As Int16

        Dim affectedRows As Int16 = 0
        If intCompKey = -1 Then
            Return affectedRows
        End If

        Try

            Dim cmd As DbCommand = m_db.GetStoredProcCommand("deleteComponent")
            m_db.AddInParameter(cmd, "@compKey", DbType.Int32, intCompKey)
            affectedRows = m_db.ExecuteNonQuery(cmd)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        Return affectedRows

    End Function


    '
    ' read all of the database records
    '
    Public Sub dbSelectAllComponents()

        Try

            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectTblComponents")
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblComponents.ToString)
            m_dt = m_ds.Tables(DC_RackTables.tblComponents.ToString)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Sub dbSelectComponentByName(ByVal strCompName As String)

        Try

            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectComponentsByName")
            m_db.AddInParameter(cmd, "@compName", DbType.String, LCase(strCompName))
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblComponents.ToString)
            m_dt = m_ds.Tables(DC_RackTables.tblComponents.ToString)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try


    End Sub

    '
    '
    '
    Public Sub dbSelectComponentByCompKey(ByVal lngCompKey As Int32)

        Try

            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectComponentByKey")
            m_db.AddInParameter(cmd, "@compKey", DbType.Int32, lngCompKey)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblComponents.ToString)
            m_dt = m_ds.Tables(DC_RackTables.tblComponents.ToString)
            m_dt.TableName = DC_RackTables.tblComponents.ToString

            ' if we find the record move it to the properties
            If m_dt.Rows.Count = 1 Then
                Dim m_dr As DataRow = m_dt.Rows(0)
                moveDataRowToProperties(m_dr)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try


    End Sub

    '
    ' we use this one to chase a collection of ports, usually for the connection data class
    '
    Public Sub dbSelectComponentByCompKeyCollection(ByVal colKeys As Collection)


        Dim dtTemp As DataTable = New DataTable
        Dim dtTempIn As DataTable = New DataTable
        Dim drTemp As DataRow
        Dim intCompKey As Int32


        Try


            ' use the collection of portkeys to create a custom portkey table
            For intCt = 1 To colKeys.Count
                If dtTempIn Is Nothing Then
                    dtTempIn = New DataTable
                Else
                    dtTempIn = Nothing
                    dtTempIn = New DataTable
                End If
                intCompKey = colKeys.Item(intCt)
                dbSelectComponentByCompKey(intCompKey)
                dtTempIn = m_dt.Copy
                If intCt = 1 Then
                    dtTemp = dtTempIn.Copy
                Else
                    For Each drTemp In dtTempIn.Rows
                        With drTemp
                            dtTemp.ImportRow(drTemp)
                        End With
                    Next
                End If
            Next intCt ' next comp key

            ' clean the data caches again
            initDataSetAndTable()

            dtTemp.TableName = DC_RackTables.tblComponents.ToString
            m_dt = dtTemp.Copy
            If m_ds.Tables(myEnum.ToString) IsNot Nothing Then
                m_ds.Tables(myEnum.ToString).Clear()
                For Each drTemp In dtTemp.Rows
                    m_ds.Tables(myEnum.ToString).ImportRow(drTemp)
                Next
            Else
                m_ds.Tables.Add(dtTemp.Copy)
            End If


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Function dbSelectComponentSiteKeyCompName(ByVal intSiteKey As Int32, _
                                                     ByVal strCompName As String) _
                                                     As Int32

        Dim lngCompKey As Int32 = -1

        Try

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectComponentSiteKeyCompName")

            m_db.AddInParameter(cmd, "@compName", DbType.String, LCase(strCompName))
            m_db.AddInParameter(cmd, "@SiteKey", DbType.Int32, intSiteKey)
            m_db.LoadDataSet(cmd, m_ds, "tempTable")

            Dim tempTable As DataTable = New DataTable
            tempTable = m_ds.Tables("tempTable")

            If 0 < tempTable.Rows.Count Then
                Dim m_dr As DataRow
                m_dr = tempTable.Rows(0)
                lngCompKey = m_dr.Item("compKey")
            End If

            tempTable = Nothing

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try


        Return lngCompKey

    End Function

    '
    '
    '
    Public Function dbSelectComponentParentKeyCompName(ByVal intParentKey As Int32, _
                                                 ByVal strCompName As String) _
                                                 As Int32

        Dim lngCompKey As Int32 = -1

        Try

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectComponentParentKeyCompName")

            m_db.AddInParameter(cmd, "@compName", DbType.String, LCase(strCompName))
            m_db.AddInParameter(cmd, "@parentKey", DbType.Int32, intParentKey)
            m_db.LoadDataSet(cmd, m_ds, "tempTable")

            Dim tempTable As DataTable = New DataTable
            tempTable = m_ds.Tables("tempTable")

            If 0 < tempTable.Rows.Count Then
                Dim m_dr As DataRow
                m_dr = tempTable.Rows(0)
                lngCompKey = m_dr.Item("compKey")
            End If

            tempTable = Nothing

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try


        Return lngCompKey

    End Function

    '
    '
    '
    Public Function dbSelectComponentRootSiteLocation(ByVal intCompKey As Int32) _
                                                 As Int32

        Dim lngSiteKey As Int32 = -1

        Try

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectComponentRootSiteLocation")

            m_db.AddInParameter(cmd, "@compKey", DbType.Int32, intCompKey)
            m_db.LoadDataSet(cmd, m_ds, "tempTable")

            Dim tempTable As DataTable = New DataTable
            tempTable = m_ds.Tables("tempTable")

            If 0 < tempTable.Rows.Count Then
                Dim m_dr As DataRow
                m_dr = tempTable.Rows(0)
                lngSiteKey = m_dr.Item("locSiteKey")
            End If

            tempTable = Nothing

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try


        Return lngSiteKey

    End Function

    '
    '
    '
    Public Function dbGetComponentLocationViewData(ByVal intCompKey As Int32) _
            As System.Data.DataTable

        Dim temp_ds As DataSet = New DataSet
        Dim temp_dt As DataTable = New DataTable


        Try

            Dim strViewLocation As String = "viewComponentLocation"

            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectComponentLocationView")
            m_db.AddInParameter(cmd, "@compKey", DbType.Int32, intCompKey)

            m_db.LoadDataSet(cmd, temp_ds, strViewLocation)
            temp_dt = temp_ds.Tables(strViewLocation)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        dbGetComponentLocationViewData = temp_dt

    End Function


    '
    '
    '
    Private Function dbSelectURRICollectionComponents _
                (ByVal colURRIKeys As Collection, _
                 ByVal intLevel As Integer) _
                 As DataTable

        Dim tempDs As DataSet = New DataSet
        Dim tempDt As DataTable = New DataTable


        Try


            ' pass in a collection of strings with unknown length (expecting a list of cabinets = 3)
            ' these are a hierarchial list of parents. The intlevel tells
            ' us how deep into the string we need to go to build our search
            ' argument to get the components
            Dim colTemp1 As New Collection ' work with the colURRIKeys
            Dim colTemp2 As New Collection ' Build the search strings
            Dim colSearchStrings As New Collection
            Dim strSearch As String = ""
            Dim intX As Integer
            Dim strURRI As String = ""
            Dim rckUrri As clsDC_RackData.rackURRI

            ' given the collection of keys and the 
            For Each rckURRI In colURRIKeys
                colTemp1 = Nothing
                colTemp1 = New Collection
                colTemp1 = BuildCsvCollection(strURRI, "int32")
                If intLevel <= colTemp1.Count Then
                    colTemp2 = Nothing
                    colTemp2 = New Collection
                    For intX = 1 To intLevel
                        colTemp2.Add(colTemp1.Item(intX))
                    Next intX
                    strSearch = BuildCsvString(colTemp2)
                    strSearch = Left(strSearch, Len(strSearch) - 1)
                    If memberOfCollection(colSearchStrings, strSearch) = False Then
                        colSearchStrings.Add(strSearch)
                    End If
                End If
            Next rckURRI


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Finally

            dbSelectURRICollectionComponents = tempDt

        End Try


    End Function


    '
    ' pass in a collection of rackkeys and return the merged component table
    '
    Public Sub GetCompositeRow _
        (ByVal colURRIKeys As Collection(Of clsDC_RackData.rackURRI))

        initDataSetAndTable()

        Dim dtInput As DataTable = New DataTable
        Dim dtOutput As DataTable = New DataTable
        Dim drTemp As DataRow
        Dim lngRackKey As Int32
        Dim colRacks As Collection = New Collection
        Dim rckUrri As clsDC_RackData.rackURRI

        For Each rckURRI In colURRIKeys
            colRacks.Add(rckUrri.rckKey)
        Next rckURRI

        Try

            For intX = 1 To colRacks.Count
                lngRackKey = colRacks(intX)
                If Not (dtInput Is Nothing) Then
                    dtInput = Nothing
                    dtInput = New DataTable
                Else
                    dtInput = New DataTable
                End If
                dtInput = GetRackComponents(lngRackKey)
                With dtInput
                    If intX = 1 Then
                        dtOutput = Nothing
                        dtOutput = New DataTable
                        dtOutput = dtInput.Copy
                    Else
                        For Each drTemp In .Rows
                            dtOutput.ImportRow(drTemp)
                        Next
                    End If
                End With
            Next intX

            dtOutput.TableName = DC_RackTables.tblComponents.ToString
            For Each drTemp In dtOutput.Rows
                m_ds.Tables(DC_RackTables.tblComponents.ToString).ImportRow(drTemp)
            Next

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' pass in a rack key and return a dataset with two tables
    ' used in composite row building and datagridview/treeview
    '

    Public Function GetRackComponents _
        (ByVal lngRackKey As Int32) _
        As DataTable

        Dim m_dt1 As DataTable = New DataTable
        Dim dtReturn As DataTable = New DataTable
        Dim m_ds1 As DataSet = New DataSet

        Try

            Dim cmd1 As DbCommand
            cmd1 = m_db.GetStoredProcCommand("selectComponentsByRack")
            m_db.AddInParameter(cmd1, "@compKey", DbType.Int32, lngRackKey)
            m_db.LoadDataSet(cmd1, m_ds1, "temp")
            m_dt1 = m_ds1.Tables("temp")

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
            m_dt1 = Nothing
        End Try

        GetRackComponents = m_dt1

    End Function

    '
    '
    '
    Public Sub GetRowComponents _
            (ByVal lngRowKey As Int32)

        Try

            initDataSetAndTable()

            Dim cmd1 As DbCommand
            cmd1 = m_db.GetStoredProcCommand("selectComponentsByRowKey")
            m_db.AddInParameter(cmd1, "@RowKey", DbType.Int32, lngRowKey)

            m_db.LoadDataSet(cmd1, m_ds, DC_RackTables.tblComponents.ToString)
            m_dt = m_ds.Tables(DC_RackTables.tblComponents.ToString)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally
        End Try

    End Sub


#End Region

#Region " Constructor "

    '
    '
    '
    Public Sub New(ByVal visApp As Microsoft.Office.Interop.Visio.Application, _
                   ByVal visDoc As Microsoft.Office.Interop.Visio.Document)


        Try



            m_db = DatabaseFactory.CreateDatabase()

            strUserId = Environment.UserDomainName & "/" & Environment.UserName

            initDataSetAndTable()



            drawingApplication = visApp
            drawingDocument = visDoc

            _clsVisio = New visRackComponentAttributes.clsVisioSupport
            _clsVisio.drawingApplication = visApp
            _clsVisio.drawingDocument = visDoc


            buildDeleteComponentCommand()
            buildInsertComponentCommand()
            buildUpdateComponentCommand()



        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try
    End Sub



    '
    ' create a csv string with end brackets from a collection
    '
    Private Function BuildCsvString _
        (ByVal colA As Collection) _
        As String

        Dim intY As Integer
        Dim strCsv As String = "[]"
        Try

            If (colA Is Nothing) _
                Or (colA.Count = 0) Then
                Exit Try
            Else
                With colA
                    ' start bracket
                    strCsv = "["
                    For intY = 1 To colA.Count
                        strCsv = strCsv & CStr(colA.Item(intY))
                        If intY <> colA.Count Then
                            strCsv = strCsv & ","
                        End If ' test for last position
                    Next intY
                    ' end bracket
                    strCsv = strCsv & "]"
                End With
            End If

        Catch ex As Exception
            '********* visio snippet vb.net visXXXX ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
            strCsv = "[]"
        End Try

        BuildCsvString = strCsv

    End Function


    '**************************************************************************
    '
    ' local string handlers
    '
    '**************************************************************************

    '
    ' use a dictionary instead
    '
    Public Function memberOfCollection _
                (ByVal colObjects As Collection, _
                ByVal objMember As Object, _
                Optional ByVal strType As String = "string") _
                As Boolean

        Dim intX As Integer
        Dim blnReturn As Boolean = False
        Dim blnObject As Boolean = True

        Select Case strType
            Case "string"
                If Not (TypeOf objMember Is String) Then blnObject = False
                ' need to be explicit on com+ objects
            Case "int32"
                If Not (TypeOf objMember Is Int32) Then blnObject = False
            Case "double"
                If Not (TypeOf objMember Is Double) Then blnObject = False
            Case "integer"
                If Not (TypeOf objMember Is Integer) Then blnObject = False
                ' where long is 32bit on 32bit machine it becomes 64bit on upscale
            Case "long"
                If Not (TypeOf objMember Is Long) Then blnObject = False
            Case Else
                blnObject = False
        End Select

        Try
            ' we passed the objecttype test
            If blnObject = True Then
                For intX = 1 To colObjects.Count
                    Select Case strType
                        Case "string"
                            If LCase(objMember) = LCase(colObjects.Item(intX)) Then
                                blnReturn = True
                                Exit For
                            End If
                        Case "long", "double", "integer", "int32"
                            If objMember = colObjects.Item(intX) Then
                                blnReturn = True
                                Exit For
                            End If
                    End Select
                Next intX
            End If

            blnReturn = True


        Catch ex As Exception
            '********* visio snippet vb.net visXXXX ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally
        End Try


        Return blnReturn

    End Function


    '
    ' pass in a bracketed string of csv separated items and pass back a collection
    ' strType allows the user to define what the collection is composed of
    '
    Public Function BuildCsvCollection _
            (ByVal strCsv As String, _
            Optional ByVal strType As String = "string", _
            Optional ByVal strDel As String = ",") _
            As Collection

        Dim colReturn As Collection
        colReturn = New Collection
        Try
            Dim intX As Integer

            ' get rid of first bracket
            If InStr(1, strCsv, "[") Then
                strCsv = Microsoft.VisualBasic.Replace(strCsv, "[", "")
            Else
                GoTo ExitHandler
            End If
            ' get rid of second bracket
            If InStr(1, strCsv, "]") Then
                strCsv = Microsoft.VisualBasic.Replace(strCsv, "]", "")
            Else
                GoTo ExitHandler
            End If

            intX = ParseItemsToCollection(strCsv, colReturn, strDel, strType)

ExitHandler:
            BuildCsvCollection = colReturn
            Exit Function

        Catch ex As Exception
            '********* visio snippet vb.net visXXXX ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
            BuildCsvCollection = colReturn
        End Try

    End Function

    '
    ' pass in a string, collection, and delimiter - update the collection and return count
    '
    Public Function ParseItemsToCollection _
        (ByVal s As String, _
        ByRef colA As Collection, _
        ByVal Delim As String, _
        Optional ByVal strType As String = "string") _
        As Integer

        Try

            '
            Dim p As Int32
            '
            ' Check for valid delimiter
            '
            If Delim = "" Then
                ParseItemsToCollection = -1
                Exit Function
            End If
            Dim intT32 As Int32

            '
            ' Copy Items
            '
            p = InStr(1, s, Delim, vbTextCompare)
            Do While p > 0

                Select Case strType
                    Case "string"
                        colA.Add(Left$(s, p - 1))
                    Case "integer"
                        colA.Add(CInt(Left$(s, p - 1)))
                    Case "double"
                        colA.Add(CDbl(Left$(s, p - 1)))
                    Case "int32"
                        intT32 = CInt(Left$(s, p - 1))
                        colA.Add(intT32)
                    Case "long"
                        colA.Add(CLng(Left$(s, p - 1)))
                End Select
                s = Mid$(s, p + 1)
                p = InStr(1, s, Delim, vbTextCompare)
            Loop
            '
            ' Copy Last Item
            '
            Select Case strType
                Case "string"
                    colA.Add(s)
                Case "integer"
                    colA.Add(CInt(s))
                Case "double"
                    colA.Add(CDbl(s))
                Case "int32"
                    intT32 = CInt(s)
                    colA.Add(intT32)
                Case "long"
                    colA.Add(CLng(s))
            End Select


        Catch ex As Exception
            '********* visio snippet vb.net visXXXX ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally

            ParseItemsToCollection = colA.Count
        End Try

    End Function



#End Region

#Region " Visio Properties "

    '
    '
    '
    Public Sub UpdateComponentProperties _
        (ByRef visShape As Microsoft.Office.Interop.Visio.Shape, _
         ByVal drComp As System.Data.DataRow)

        Dim visCell As Microsoft.Office.Interop.Visio.Cell

        Try


            If Not (drComp Is Nothing) Then
                moveDataRowToProperties(drComp)
            Else
                Exit Sub
            End If

            With m_dc
                _clsVisio.ForceShapeData(visShape, "compName", .compName, "attrComponent")
                _clsVisio.ForceShapeData(visShape, "compLabel", .compLabel, "attrComponent")
                _clsVisio.ForceShapeData(visShape, "compKey", .compKey, "attrComponent")
                _clsVisio.ForceShapeData(visShape, "compVendorKey", CStr(.compVendorKey), "attrComponent")
                _clsVisio.ForceShapeData(visShape, "compType", CStr(.compType), "attrComponent")
                _clsVisio.ForceShapeData(visShape, "compSubType", CStr(.compSubtype), "attrComponent")
                _clsVisio.ForceShapeData(visShape, "compMoveStatus", CStr(.compMoveStatus), "attrComponent")
                _clsVisio.ForceShapeData(visShape, "compParentSlot", .compParentSlot, "attrComponent")
                _clsVisio.ForceShapeData(visShape, "compParentSubType", CStr(.compParentSubType), "attrComponent")
                ' we need a view attribute, which is not saved in the database.....
                _clsVisio.ForceShapeData(visShape, "compView", .compView, "attrComponent")

            End With




            ' add the ability to turn the text on and off
            _clsVisio.ForceUserProperty(visShape, "HideText", "True")

            ' set the hidetext field to true 
            If visShape.CellsSRCExists(VisSectionIndices.visSectionObject, VisRowIndices.visRowMisc, VisCellIndices.visHideText, False) = True Then
                visCell = visShape.CellsSRC(VisSectionIndices.visSectionObject, VisRowIndices.visRowMisc, VisCellIndices.visHideText)
                _clsVisio.SetCellValueToString(visCell, "true")
            End If

            ' add the ability to turn the shape on and off
            _clsVisio.ForceUserProperty(visShape, "HideNodeShape", "False")
            If visShape.CellsSRCExists(VisSectionIndices.visSectionFirstComponent, VisRowIndices.visRowComponent, VisCellIndices.visCompNoShow, False) = True Then
                visCell = visShape.CellsSRC(VisSectionIndices.visSectionFirstComponent, VisRowIndices.visRowComponent, VisCellIndices.visCompNoShow)
                visCell.FormulaU = "user.HideNodeShape"
            End If


        Catch com As COMException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    ' definitions for the containers
    Private Const constCompPinX As String = "1.5 in"
    Private Const constCompPinY As String = "9.875 in"

    '
    ' based on the component key, read the component record from the data source
    '
    Public Function buildComponentContainer _
                        (ByVal intCompKey As Int32, _
                        ByVal _clsData As clsDC_RackData) _
                        As Microsoft.Office.Interop.Visio.Shape



        Dim shpContainer As Microsoft.Office.Interop.Visio.Shape = Nothing

        Try

            drawingDocument = drawingPage.Document
            _clsVisio.drawingPage = drawingPage


            dbSelectComponentByCompKey(intCompKey)

            Dim colInput As Collection = New Collection
            Dim componentQuery = From component In _clsData.m_ds.Tables(DC_RackTables.tblComponents.ToString).AsEnumerable() _
                       Where component!compkey = intCompKey
                      Select component

            Dim drTemp As System.Data.DataRow = Nothing
            ' we should only have transferred in a single component key
            If componentQuery.Count = 1 Then
                For Each c In componentQuery
                    colInput.Add("compKey_" & c.Item("compKey").ToString)
                    'colInput.Add(c.Item("compName").ToString)
                    drTemp = c
                Next c
            End If

            ' create a list control
            Dim listControl As Microsoft.Office.Interop.Visio.Shape = Nothing
            ' pass in the created collection and return a populated list control
            Dim colMembers As Collection = New Collection


            ' get the datagraphic
            Dim strStencil As String = ""
            Dim strMaster As String = ""
            Dim strDGMaster As String = ""
            Dim visDG As Microsoft.Office.Interop.Visio.Master = Nothing
            Dim intLabelFont As Integer
            Dim intValueFont As Integer
            Dim dblCalloutWidth As Double
            Dim dgmasterQuery = From datagraphic In _clsData.m_ds.Tables(DC_RackTables.tblDGMasters.ToString).AsEnumerable() _
                                Where datagraphic!dgmTable = DC_RackTables.tblComponents.ToString _
                                Select datagraphic

            ' should only be one..
            For Each m In dgmasterQuery
                If LCase(m!dgmTable) = LCase(DC_RackTables.tblComponents.ToString) Then
                    strStencil = m!dgmSurrogateStencil
                    strStencil = _CustomStencilPath & strStencil
                    strMaster = m!dgmSurrogateMaster
                    intLabelFont = CInt(m!dgmFontLabel)
                    intValueFont = CInt(m!dgmFontValue)
                    dblCalloutWidth = CDbl(m!dgmCalloutWidth)
                    strDGMaster = m!dgmMaster
                    Exit For
                End If
            Next

            ' pass in the created collection and return a populated list control
            colMembers = _clsVisio.dropDropDownBoxItems(colInput, strMaster, strStencil, "1.5 in", "1 in", vbTrue)
            Dim strName As String = ""
            Dim strKey As String = ""

            Dim visShape As Microsoft.Office.Interop.Visio.Shape = Nothing
            Dim visCell As Microsoft.Office.Interop.Visio.Cell = Nothing

            ' should only be one
            For Each shapeid In colMembers

                visShape = drawingPage.Shapes.ItemFromID(shapeid)

                If visShape.CellExists("user.attrkey", False) = True Then
                    strName = visShape.Cells("user.attrkey").ResultStr("")
                Else
                    strName = ""
                    GoTo NextShape
                End If
                ' clear any shape data properties
                _clsVisio.deleteShapeData(visShape)

                ' set it semi-transparent
                Dim strTrans As String = "50%"
                ' first the fill properties
                _clsVisio.recurseApplyCellFormula(visShape, VisSectionIndices.visSectionObject, VisRowIndices.visRowFill, VisCellIndices.visFillForegndTrans, strTrans)
                _clsVisio.recurseApplyCellFormula(visShape, VisSectionIndices.visSectionObject, VisRowIndices.visRowFill, VisCellIndices.visFillBkgndTrans, strTrans)
                ' then the line properties
                _clsVisio.recurseApplyCellFormula(visShape, VisSectionIndices.visSectionObject, VisRowIndices.visRowLine, VisCellIndices.visLineColorTrans, strTrans)
                ' prep it for applying the datagraphics by applying the data from the dgmasters table
                _clsVisio.addDataGraphicUserFieldsToShape _
                        (visShape, _
                        intValueFont.ToString, _
                        intLabelFont.ToString, _
                        dblCalloutWidth.ToString)

                'Check that the requested Data Graphic Master exits
                For Each visMaster In drawingDocument.Masters
                    If visMaster.Type = VisMasterTypes.visTypeDataGraphic Then
                        If visMaster.Name = strDGMaster Then
                            visDG = visMaster
                            Exit For
                        End If
                    End If
                Next

                UpdateComponentProperties(visShape, drTemp)

                visShape.Text = drTemp.Field(Of String)("compName")

                ' move the component name to comment field so we get mouse over
                If visShape.CellsSRCExists(VisSectionIndices.visSectionObject, VisRowIndices.visRowMisc, VisCellIndices.visComment, False) = True Then
                    visCell = visShape.CellsSRC(VisSectionIndices.visSectionObject, VisRowIndices.visRowMisc, VisCellIndices.visComment)
                    _clsVisio.SetCellValueToString(visCell, strName)
                End If


                ' default for component class is to hide text so make sure we do not
                If visShape.CellsSRCExists(VisSectionIndices.visSectionObject, VisRowIndices.visRowMisc, VisCellIndices.visHideText, False) = True Then
                    visCell = visShape.CellsSRC(VisSectionIndices.visSectionObject, VisRowIndices.visRowMisc, VisCellIndices.visHideText)
                    _clsVisio.SetCellValueToString(visCell, "false")
                End If

                ' allow the legend to keep track of the shapes
                _clsVisio.ForceUserProperty(visShape, "visLegendShape", "2.0")

                'Apply the Data Graphic to the Shape
                visShape.DataGraphic = visDG

                ' unlock the groups so we can change the font sizes
                _clsVisio.recurseApplyCellFormula(visShape, VisSectionIndices.visSectionObject, VisRowIndices.visRowLock, VisCellIndices.visLockGroup, "0")

                If visShape.CellExists("user.dbIntLabelFont", False) = True Then

                    ' get the loaded values 
                    intLabelFont = CInt(visShape.Cells("user.dbintlabelfont").ResultIU)
                    intValueFont = CInt(visShape.Cells("user.dbintvaluefont").ResultIU)
                    dblCalloutWidth = CDbl(visShape.Cells("user.dbdblcallout").ResultIU)

                    ' set the callout width = 85% of the surrogate shapes width (as a formula) so that it fits inside the shape
                    Dim strId As String = visShape.NameID & "!Width*.85"
                    strId = "Guard(" & strId & ")"

                    _clsVisio.recurseApplyCustomFormula(visShape, "prop.msvcalloutproplabelsize", intLabelFont.ToString & " pt")
                    _clsVisio.recurseApplyCustomFormula(visShape, "prop.msvcalloutpropvaluesize", intValueFont.ToString & " pt")
                    _clsVisio.recurseApplyCustomFormula(visShape, "prop.msvcalloutpropwidth", strId)

                End If
NextShape:
            Next shapeid


            shpContainer = _clsVisio.addContainerToPage("Container 4", "", "")
            'shpContainer.Name = "contComponent"
            'shpContainer.NameU = "contComponent"

            listControl = _clsVisio.loadListControl(colMembers, VisListAlignment.visListAlignLeftOrTop, VisListDirection.visListDirTopToBottom)
            'listControl.Name = "listComp"
            'listControl.NameU = "listComp"

            ' add the list control to the container
            _clsVisio.AddShapeToContainer(shpContainer, listControl)

            shpContainer.Cells("pinx").Formula = constCompPinX
            shpContainer.Cells("piny").Formula = constCompPinY



        Catch com As COMException
            '********* visio snippet vb.net vis0056vb ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch sql As SqlException
            '********* visio snippet vb.net vis0057vb ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            '********* visio snippet vb.net vis0059vb ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If

        End Try

        Return shpContainer

    End Function


#End Region











End Class
